import pymysql
from todo import Todo

class Todomanage_mysql:
    @staticmethod
    # def get_connection(db='test'):
    #     return pymysql.Connection(host='mysql',user='root',password='123456',
    #                               cursorclass=pymysql.cursors.Cursor,database= 'db',autocommit=True)
    def get_connection():
        return pymysql.Connect(host='192.168.190.128',user='root',
                                  password='root@123ABC',database= 'Task',autocommit=True)
    def init_db (self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS todos(
                todo_id int NOT NULL AUTO_INCREMENT,
                title VARCHAR(127) NOT NULL DEFAULT(''),
                complete int NOT NULL DEFAULT(0) ,
                PRIMARY KEY (todo_id)
                )
            ''' )

    def convert_row_to_object(self, row):
         return Todo(row[1], id=row[0], completed=True if row[2] == 1 else False)

    def get_list(self):
        with self.get_connection() as conn:
             curses = conn.cursor()
             curses.execute('select * from todos')
             todo_list = curses.fetchall()
             object_list = []
             for row in todo_list:
                 object_list.append(self.convert_row_to_object(row))
             return object_list

    def create(self,title):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("insert into todos(title) values(%s)",(title,))
            cursor.execute("select last_insert_id()")
            (title,) = cursor.fetchone()
        return Todo(title,id)

    def get_todo_byid(self,todo_id):
        with self.get_connection() as  conn:
            cursor=conn.cursor()
            cursor.execute('select * from todos where todo_id=%s',(todo_id,))
            todo=cursor.fetchone()
            if todo is not None:
             return self.convert_row_to_object(todo)
            else:
                return None
    def mark_completed(self,todo_id):
        todo = self.get_todo_byid(int(todo_id))
        if todo is not None:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("update todos set complete=1 where todo_id=%s",(todo_id,))
                data = cursor.fetchone()
        return Todo(data)

    def delete(self,todo_id):
        todo = self.get_todo_byid(todo_id)
        if todo is not None:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("delete from todos where todo_id=%s", (todo_id,))
            return todo


